package com.zkr.thresho.service;

import com.zkr.thresho.mapper.ThreshoMapper;
import com.zkr.thresho.model.*;
import com.zkr.util.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.InputStream;
import java.util.*;

/**
 * Created by lenovo on 2021/1/8.
 */
@Service
public class ThreshoServiceImpl implements ThreshoService {


    @Autowired
    private ThreshoMapper threshoMapper;

    //分页查询
    @Override
    public ResultVo queryPage(ThreshoSelectVo threshoSelectVo) {
        threshoSelectVo.setUserId(1);
        int count = threshoMapper.queryCount(threshoSelectVo);

        List<ThreshoSelectVo> threshoSelectVoList = threshoMapper.query(threshoSelectVo);

        AllRecords allRecords = new AllRecords();
        allRecords.setPageIndex(threshoSelectVo.getPageIndex());
        allRecords.setDataList(threshoSelectVoList);
        allRecords.setPageSize(threshoSelectVo.getPageSize());
        allRecords.setTotalNumber(count);
        allRecords.resetTotalNumber(count);
        return ResultBuilderVo.success(allRecords);
    }


    @Override
    public ResultVo exportFile(ThreshoSelectVo threshoSelectVo, HttpServletResponse response)throws Exception {
        //查询数据
        List<ThreshoSelectVo> threshoSelectVoList = threshoMapper.queryExportData(threshoSelectVo);
        /*for(ThreshoSelectVo threshoSelectVo1:threshoSelectVoList){
            Integer threshonum = threshoSelectVo1.getThreshonum()==true?1:0;
            threshoSelectVo1.setThreshonum(threshonum);
        }*/
        if (threshoSelectVoList.size()>5000){
            return ResultBuilderVo.error("当前数据条数为"+threshoSelectVoList.size()+",数据大于5000条，请重新选择条件，数据小于5000条才能导出！");
        }
        //获取响应输出流
        ServletOutputStream out = response.getOutputStream();
        //给输出文件设置名称
        POIClass.toPackageOs(response, "备件数量阀值");
        //读取模板中的数据
        InputStream in = ExportUtil.toPackageIn("static/备件数量阀值导出.xlsx");
        //根据模板的数据、把查询出来的数据给摸版SHeet1组中的数据赋值、把excel输出到浏览器上
        writeDataToExcel(in, "Sheet1", threshoSelectVoList, out);
        if (in != null) {
            in.close();
            out.close();
        }
        return null;
    }

    // 由于此方法不能通用, 所以单独写在这里
    private void writeDataToExcel(InputStream in, String sheetName, List<ThreshoSelectVo> resultList, ServletOutputStream out) throws Exception {
        //POi读取模板
        XSSFWorkbook wb = new XSSFWorkbook(in);
        //读取sheet1中的数据
        Sheet sheet = wb.getSheet(sheetName);
        if (sheet != null) {
            //向sheet1中赋值，设置样式
            toResultListValueInfo(sheet, resultList);
        }
        //把数据写入到输出流中
        wb.write(out);
        //关闭poi方法
        wb.close();
    }

    /**
     * 插入excel表中项目信息
     *
     * @param sheet
     */
    private void toResultListValueInfo(Sheet sheet, List<ThreshoSelectVo> plantList) {
        //从第4行开始赋值
        int row_column = 5;
        int xuhao =1;
        //遍历数据集合
        for (ThreshoSelectVo obj : plantList) {
            //创建一行的方法
            Row row = sheet.createRow(row_column);
            // 给第一列序号赋值赋值
            POIClass.toCellValue(row,0, xuhao + "");
            // 给第二列编码赋值
            POIClass.toCellValue(row, 1, obj.getLegalPersonCode() + "");
            // 给第3列名称赋值
            POIClass.toCellValue(row, 2, obj.getMaterialCode()+ "");
            // 给状态赋值
            POIClass.toCellValue(row, 3, obj.getThreshonum() + "");
            //给描述赋值
            POIClass.toCellValue(row, 4, obj.getRemark() + "");
            row_column++;
            xuhao++;
        }
    }



    /**
     *
     * @param userid
     * @return
     */
    @Override
    public ResultVo queryLegalPersonById(Integer userid) {
        List<LegalPersonVo> legalPersonVoList = threshoMapper.queryLegalPersonById(userid);

        return ResultBuilderVo.success(legalPersonVoList);

    }

    @Override
    public ResultVo querySubClass() {
        List<SubClassVo> subClassVoList = threshoMapper.querySubClass();
        return ResultBuilderVo.success(subClassVoList);

    }
     @Override
    @Transactional
    public ResultVo add(List<ThreshoAddVo> threshoAddVoList) {
        List<ThreshoAddVo> spareVooCode =  threshoMapper.queryByCode(threshoAddVoList);
        if (spareVooCode.size() > 0){
            return ResultBuilderVo.error("同一法人下的法人编码已存在");
        }
        /*SpareSelectVo spareCode =  threshoMapper.queryByCode(threshoAddVoList);
        if (spareCode!=null){
            return ResultBuilderVo.error("备件编码已经存在了");
        }*/

        List<ThreshoAddVo> repeat = threshoMapper.queryThresho(threshoAddVoList);
        if (repeat.size()>0){
            return ResultBuilderVo.error("阀值不能重复"+repeat);
        }
        Date nowDate = new Date();

        for (ThreshoAddVo threshoAddVo : threshoAddVoList) {
            threshoAddVo.setCreateName("李四");
            threshoAddVo.setCreateTime(new Date());
            threshoAddVo.setUpdateName("李四");
            threshoAddVo.setUpdateTime(new Date());
        }

        //入库
        threshoMapper.insertPlantClass(threshoAddVoList);
        return ResultBuilderVo.success();

    }

    @Override
    public ResultVo queryLagalSaper(LagalSpareVo lagalSpareVo) {
        List<LagalSpareVo> lagalSpareVoList = threshoMapper.queryLagalSaper(lagalSpareVo);
        return ResultBuilderVo.success(lagalSpareVoList);

    }

    @Override
    public ResultVo querySpare(SpareSelectVo spareSelectVo) {
        List<SpareSelectVo> spareSelectVoList = threshoMapper.querySpare(spareSelectVo);
        return ResultBuilderVo.success(spareSelectVoList);
    }

    @Override
    @Transactional
    public ResultVo update(@Valid ThreshoUpdateVo threshoUpdateVo) {

        SpareSelectVo spareName = threshoMapper.queryThreshoId(threshoUpdateVo.getThreshoId());
        if (spareName==null){
            return ResultBuilderVo.error("修改数据不存在");
        }
        SpareSelectVo spareSelectVo = new SpareSelectVo();
        BeanUtils.copyProperties(threshoUpdateVo, spareSelectVo);
        spareSelectVo.setUpdateTime(new Date());
        spareSelectVo.setUpdateName("李四");
        threshoMapper.update(spareSelectVo);
        return ResultBuilderVo.success();

    }

    @Override
    public ResultVo importFile(MultipartFile file) throws Exception{
        //poi导入
        //1.判断是否为空
        //2.判断版本
        //3.使用POI读取到文件的内容
        //4.把文件中的内容解析,放入到list对象中



        //判断是否为空
        if(file==null){
            return ResultBuilderVo.error("导入文件不能为空!");
        }
        //判断版本
        if (!file.getOriginalFilename().contains(".xlsx")){
            return ResultBuilderVo.error("请上传2007版本的,以.xlsx结尾的表格!");
        }

        ResultVo<List<ThreshoAddVo>> resultVo = poiImport(file);
        System.out.println(resultVo.getData());
        if (!resultVo.getSuccess()){
            return resultVo;
        }


        //.验证是否为空，
        // .验证长度是否合法（阀值设置数以及描述字数）
        ResultVo<List<ThreshoAddVo>> validate = validate(resultVo.getData());
        if (!validate.getSuccess()){
            return validate;
        }
        //验证法人是否存在
        ResultVo<List<ThreshoAddVo>> validatePerson = validatePerson(resultVo.getData());
        if (!validatePerson.getSuccess()){
            return validatePerson;
        }
        // 是否重复
        ResultVo<List<ThreshoAddVo>> validatee = validatee(resultVo.getData());
        if (!validatee.getSuccess()){
            return validatee;
        }

       /* ResultVo<List<ThreshoAddVo>> resultVo2 = databaseNotNull(resultVo1.getData());
        if (!resultVo2.getSuccess()){
            return resultVo2;
        }*/

        //6.批量入库
        ResultVo add = add(validate.getData());
        if (!add.getSuccess()){
            return add;
        }
        return  ResultBuilderVo.success();
    }




    private ResultVo<List<ThreshoAddVo>> validatee(List<ThreshoAddVo> threshoAddVoList) {
        //判断阀值重复
        TreeMap<ThreshoAddVo,ThreshoAddVo> treeMap = new TreeMap<>(new Comparator<ThreshoAddVo>() {
            @Override
            public int compare(ThreshoAddVo o1, ThreshoAddVo o2) {
                return o1.getLegalPersonName().equals(o2.getLegalPersonName())?(o1.getLegalPersonCode().equals(o2.getLegalPersonCode())?0:1):1;
            }
        });
        List<ThreshoAddVo> list = new ArrayList<>();
        for(ThreshoAddVo threshoAddVo:threshoAddVoList){
            ThreshoAddVo put = treeMap.put(threshoAddVo, threshoAddVo);
            if(put!=null){
                list.add(put);
            }
        }

        if (list.size()>0){
            return ResultBuilderVo.error("Excel中有重复阀值："+list);
        }
        return ResultBuilderVo.success(threshoAddVoList);
    }

    //法人是否存在
    private ResultVo<List<ThreshoAddVo>> validatePerson(List<ThreshoAddVo> threshoAddVoList) {
        List<String> person = new ArrayList<>();
        for (ThreshoAddVo threshoAddVo : threshoAddVoList) {
            person.add(threshoAddVo.getLegalPersonCode());
        }
        List<String> stringList = threshoMapper.queryLegalPersonCode(person);
        List<String> personCode = new ArrayList<>();
        for (ThreshoAddVo threshoAddVo : threshoAddVoList) {
            if (!stringList.contains(threshoAddVo.getMaterialCode())) {
                personCode.add(threshoAddVo.getMaterialCode());
            }
        }
        if (personCode.size() > 0) {
            return ResultBuilderVo.error("法人编码不存在" + personCode);
        }
        return ResultBuilderVo.success(threshoAddVoList);
    }

    private ResultVo validate(List<ThreshoAddVo> threshoAddVoList) {
        //5.进行各种验证-  1.验证是否为空2.验证长度是否合法 3.状态是否合法
        //4.状态数值转换
        if (CollectionUtils.isEmpty(threshoAddVoList)){
            return ResultBuilderVo.error("请检查数据，导入数据不能为空！");
        }
//        List<String> errMessage = new ArrayList<>();
        for (ThreshoAddVo bigClassVo:threshoAddVoList){
            if (StringUtils.isEmpty(bigClassVo.getMaterialCode())){
                return ResultBuilderVo.error("备件编码不能为空！");
//                errMessage.add()
            }else{
                if (bigClassVo.getMaterialCode().length()>100){
                    return ResultBuilderVo.error("备件编码长度过长！");
                }
            }
            if (StringUtils.isEmpty(bigClassVo.getLegalPersonName())){
                return ResultBuilderVo.error("法人名称不能为空！");
            }else{
                if (bigClassVo.getLegalPersonName().length()>100){
                    return ResultBuilderVo.error("法人名称长度过长！");
                }
            }
            if (bigClassVo.getThreshonum()==null||bigClassVo.getThreshonum()==0){
                return ResultBuilderVo.error("阀值不能为空");
            }else {
                if (bigClassVo.getThreshonum()>9999){
                    return ResultBuilderVo.error("阀值数量不能超过9999");
                }
            }
        }
        return ResultBuilderVo.success(threshoAddVoList);
    }

    //poi读取信息
    private ResultVo poiImport(MultipartFile file) throws Exception{
        //        3.模板是否是需要导入的模板，验证模板是否正确
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        //获取单元格中的信息 at0是获取sheet1中的数据。
        XSSFSheet sheet = workbook.getSheetAt(0);
        System.out.println(sheet.getRow(sheet.getFirstRowNum()).getCell(0));
        //验证第一列第一行的表格标头信息是否为 “询价单导入”，如果不是，提示模板错误。
        if (!String.valueOf(sheet.getRow(sheet.getFirstRowNum()).getCell(0)).equals("备件数量阈值导入模板")) {
            return ResultBuilderVo.error("模板错误，请检查模板！");
        }
        List<ThreshoAddVo> planClassVoList = new ArrayList<>();
        //遍历excel表格中的所有数据，从第五行开始读取，没有数据时终止遍历。
        for (int i = sheet.getFirstRowNum() + 4; i <= sheet.getLastRowNum(); i++) {
            //读取每一行的数据
            XSSFRow xssfRow = sheet.getRow(i);
            if (xssfRow != null) {
                String test1 = String.valueOf(xssfRow.getCell(1)).trim();
                String test2 = String.valueOf(xssfRow.getCell(2)).trim();
                String test3 = String.valueOf(xssfRow.getCell(3)).trim();
                if (!test1.equals("") && !test1.equals("null")
                        || !test2.equals("") && !test2.equals("null")
                        || !test3.equals("") && !test3.equals("null")) {
                    //把每一行的数据放入到实体类中
                    ResultVo<ThreshoAddVo> inquiryImportVo = build(xssfRow);
                    if (!inquiryImportVo.getSuccess()) {
                        return inquiryImportVo;
                    }
                    //把实体类中的数据放入到list集合中
                    planClassVoList.add(inquiryImportVo.getData());
                }
            }
        }
        return ResultBuilderVo.success(planClassVoList);
    }

    private ResultVo<ThreshoAddVo> build(XSSFRow xssfRow) {
        ThreshoAddVo planClassAddVo = new ThreshoAddVo();
        //        4.格式问题，必须保持统一格式，统一为字符串格式导入
        planClassAddVo.setLegalPersonName(String.valueOf(xssfRow.getCell(1)));
        planClassAddVo.setMaterialCode(String.valueOf(xssfRow.getCell(2)));
       planClassAddVo.setThreshonum(Integer.valueOf((String.valueOf(xssfRow.getCell(3)))));
        planClassAddVo.setRemark(String.valueOf(xssfRow.getCell(4)));
        return ResultBuilderVo.success(planClassAddVo);
    }



}
